Data File Guidelines

Learn how to prepare your data files before you send them to Visier.

Visier relies on text files to load data from your source systems. For the platform to understand your data, it is essential all files are provided in a structured format.

This guide describes important guidelines to follow when sending data files to Visier including:

• Supported file formats

• File encoding

• Non-critical fields

• Naming conventions

• File consistency for production

In addition to the data file guidelines, the Visier Data Dictionaries document the datasets and data elements included in each application. Each Visier application has its own data dictionary that covers the details of the files' format and content for required and non-critical fields.

What are the supported file formats?

We support data in any of the following formats:

• Pipe delimited text (.txt)

• Comma-separated values (.csv)

  • Make sure to use double quotation marks to enclose each field. This is to avoid issues when a comma is part of any of the field values.

• Tab-separated values (.tsv)

• Excel 2017 and later (.xlsx) and Excel 97-2004 (.xls)

  • Ensure that the Excel files do not contain any formulas and only contain one sheet per file.

Pipe delimited text files

As an example, let's say the data in the following table is what you want to extract and include in your pipe delimited text file.

Snapshot Date	First Name	Last Name
2019-01-10	Kasimir	Chavez
2019-01-17	Byron	Charles

If this were seen in a text file with the pipe character "|" as a delimiter, it would look like this:

Snapshot Date|First Name|Last Name
2014-01-10|Kasimir|Chavez
2014-01-17|Byron|Charles

Field delimiter

We recommend a pipe ("|") and tabulation or comma (",") characters as a field delimiter. When the chosen delimiter is part of any of the field values, Visier expects additional quote delimiters.

The use of other field delimiters may lead to data load problems. For example, it is very common to have commas in a job title; in a comma separated value text file, the job title would be seen as two separate fields. See example below.

Event Date	Person Name	Job Title	Favorite Color
2019-01-10	Kasimir Chavez	Sr. Business Analyst	blue
2019-01-17	Byron Charles	Director, PMO	white
2019-02-23	Kimberly Powers	HR Business Partner	red

This file is using the correct delimiter format:

"Event Date", "Person Name", "Job Title", "Favorite Color"
"2019-01-10", "Kasimir Chavez", "Sr. Business Analyst", "blue"
"2019-01-17" "Byron Charles", "Director, PMO", "white"
"2019-02-23", "Kimberly Powers", "HR Business Partner", "red"

This file is not using the correct delimiter format, as row 3 reads as 5 columns and all other rows read as 4 columns.

Event Date, Person Name, Job Title, Favorite Color
2019-01-10, Kasimir Chavez, Sr. Business Analyst, blue
2019-01-17, Byron Charles, Director, PMO, white
2019-02-23, Kimberly Powers, HR Business Partner, red

Excel files

Excel files can be loaded into Visier, however there are a few things to note.

• Both .xlsx (Excel 2007 and later) and .xls (Excel 97-2004) formats are supported.

• Using Excel to produce your data files may apply formatting to the raw data which can lead to data load problems. Known issues include:

  • The removal of leading zeros (001234 becomes 1234),

  • Changed date formats (04/03/2014 becomes Apr-03-14),

  • Changed numeric formats (55123.00 becomes 55,123.00).

• Visier only reads the first worksheet; if there are multiple worksheets to load, you must load one worksheet per workbook.

• Visier does not read the result of any formulas, including vlookups. As a result, do not provide formulas in the worksheet, but rather use the expected value.

File encoding

File encoding is the character set used for the files. Visier supports different types of encoding, however we recommend using UTF-8 encoding. If the files have an encoding other than UTF-8, please contact Visier Technical Support.

Non-critical fields

Not all fields documented in the Visier People Data Dictionaries are mandatory, therefore Visier recommends that you do not include any non-critical fields if you don't have the data to populate them in your system.

In cases where you're planning to start populating those fields in the near future, the recommendation is to include them with empty (NULL) values.

How should the files be named?

Visier’s suggested naming convention is based on two types of files, Profile dataset such as the Employee Profile, and Event dataset such as Hire and Termination Events.

• Profile dataset: A profile dataset is a snapshot of your data at a certain point in time, typically at the end of a relevant time period. Visier expects profile files to contain one record per item that reflects the state of that item as of that point in time. For example, a month-end employee profile file should contain all employee records on the last day of the month; there should be one record per employee that reflects the state of that employee on the last day of the month.

• Event dataset: An event dataset captures every relevant change to an item over a time period. Relevant changes include updates to attributes such as status change, or the creation of new records such as new hire, new termination, performance rating, and payroll.

  • Event files require an event date and event type, which are respectively the effective date of the change and a label that identifies the change; for example, Hire for a new hire, Bonus for a bonus payment, and Approved when a requisition is approved.

  • Event files also require the identifier of the item the change applies to; for example, employee ID, requisition number, and so forth.

  • There may be zero or multiple records per item. For example, if an employee is hired, terminated, and rehired in the same month, the hire and termination event file for that month contains three records for that employee—one record per event.

Data files should have the following name formats.

File Type	Name Format	Details
Profile	profile_filename_prefix_YYYYMM_YYYYMMDD	YYYYMM is the fiscal period and YYYYMMDD is the extraction date.
Event	event_filename_prefix_YYYYMMDD	YYYYMMDD is the extraction date.

For example:

• An employee profile file for fiscal period May 2015 extracted on July 1, 2015 should be named employee_profile_201505_20150701.txt.
• A termination event file extracted on July 1, 2015 should be named terminations_20150701.txt.

File consistency for production

After your data is onboarded, the process of updating the data on a regular cadence becomes automated. For this automation to be successful it's important to maintain consistency in the file or database structures, specifically the following:

• File, table, or view names: File, table, or view names that are different than the expected pattern defined during onboarding could cause a load to fail or files becoming detached from their source.

• Unexpected file encoding: If the data file has a different encoding the conversion process can trigger characters to change to unexpected values that can cause a load failure, or can cause unexpected characters to show in the application.

• Column names: It's important that column names remain consistent. A mandatory column that's named differently is considered missing and causes a load failure.

• Numeric columns: If non-numeric characters are included in a numeric field, the data load fails.

• Date columns format: During the onboarding of your data, date columns are configured with the format received and are expected to stay consistent in all regular uploads of your production data on an ongoing basis. For example, although 03/04/2019 and March 4, 2019 are the same date, the one that doesn’t match the expected format is not recognized and causes a load failure.

Uploading your data files

As part of Visier's security policy, Visier employees cannot accept any data by email. However, you can upload files to Visier—including text, screen captures, and documents using the following methods:

• Solution Upload

• SFTP Upload

For more information on how to upload data files, see File Upload.